RDBMS SOLUTION


ASSIGNMENT 4 

SET B Q. 1 SOLUTION

Write function to print the total number of suppliers whosupplies “Keyboard”.




Source code:

    

    /*set b q 1 solution

    in this first we will create a 3nf database with 3 tables

    1:item table

    2:supplier table

    3: item_supplier table which will contain following columns:

        item_number,supplier_number,rate and quantity*/

/*Creating table*/

create table item

(

    itemno number not null primary key,

    item_name varchar2(40)

)

create table supplier

(

    supplierno number not null primary key,

    supplier_name varchar2(40),

    address varchar2(40),

    city varchar2(40)

)

create table i_s

(

    item_number number not null references item(itemno),

    supplier_number number not null references supplier(supplierno),

    rate number,

    quantity number

)

/*inserting records*/

insert into item values(1,'keyboard')

insert into item values(2,'Harddisk')

insert into item values(3,'charger')

insert into supplier values(1,'mr.Patil','a1','c1')

insert into supplier values(2,'mr.Rajan','a2','c2')

insert into supplier values(3,'mr.shekhar','a3','c3')

insert into i_s values(1,1,2000,200)

insert into i_s values(1,2,3000,300)

insert into i_s values(1,3,4000,400)

insert into i_s values(2,1,6000,600)

insert into i_s values(3,2,7000,900)

insert into i_s values(3,1,1000,2000)

/*function*/

create or replace function get_sup(ino in out number)

return number is sno number;

begin

select itemno into ino from item where item_name='keyboard';

select count(supplier_number) into sno from supplier s,i_s si 

where si.item_number=ino and si.supplier_number=s.supplierno;

return sno;

end;

/*execution*/

declare

ssno number;

ino number;

begin

ssno:=get_sup(ino);

dbms_output.put_line('total number of suppliers who sell keyboard = '||ssno);

end;

/*source code in description*/
 Download code         next